import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt

host = "cd-cdb-5st0yfog.sql.tencentcdb.com"
port= 63980
user = "root"
password = "cdbeyond-mysql-Aa123456"
database = "iot_product"

# 创建数据库连接 engine
engine = create_engine(f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")

# SQL查询语句
sql = """
select count(tpd.id) num, m.name as manufacture  
from tt_product_device tpd 
inner join tt_product_model tpm  on tpd.product_id = tpm.id
inner join tt_manufacturer m on tpm.manufacturer_id = m.id
where tpm.connected ='1' and year(m.crt_time) = 2024
and year(tpd.crt_time) = 2024 
group by manufacture
"""

# 使用pandas的 read_sql_query函数执行SQL语句，并获取返回结果
df = pd.read_sql_query(sql, engine)

# Matplotlib 绘制线图
plt.figure(figsize=(10,6))
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']  # 指定默认字体
plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像时的负号问题
plt.bar(df['manufacture'], df['num'])  
plt.xlabel('厂商')
plt.ylabel('数量')
plt.title('24年新厂商对接')  # 添加图表标题
plt.xticks(rotation='vertical')  # 旋转 x 轴标签避免重叠
plt.tight_layout()  # 自动调整子图参数以给定指定的填充
plt.show()
